package com.joinway.framework.text.sql;

import java.io.StringReader;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;

import com.joinway.framework.text.sql.bean.Column;
import com.joinway.framework.text.sql.bean.Table;
import com.joinway.framework.text.sql.constant.MySqlKeyWords;

public final class DdlParser {

	private final static String CREATE_TABLE_PATTERN = "(?i)\\s*CREATE\\s+TABLE\\s+`?(\\w+)`?.*";
	private final static String COLUMN_PATTERN = "(?i)\\s*`?(?<name>\\w+)`?\\s+(?<type>\\w+)(?<size>\\(\\d+(,\\d+)?\\))?\\s+(?<sign>UNSIGNED)?.*";
	private final static String PRIMARY_KEY_PATTERN = "(?i)\\s*PRIMARY\\s+KEY\\s+\\(\\s*(?<keys>.+)\\s*\\).?";
	
	private final static Pattern columnNamePattern = Pattern.compile(COLUMN_PATTERN);
	private final static Pattern primaryKeyPattern = Pattern.compile(PRIMARY_KEY_PATTERN);
	private final static Pattern tableNamePattern = Pattern.compile(CREATE_TABLE_PATTERN);
	
	final static List<String> COLUMN_EX_KEYS = Arrays.asList(new String[]{"create", "primary", "unique"});
	
	public static Table parse(String ddl) throws Exception {
		List<String> lines = IOUtils.readLines(new StringReader(ddl));
		
		Table table = new Table();
		table.setName(getTableName(lines));
		table.setColumns(getColumns(lines));
		
		return table;
	}
	
	protected static String getTableName(List<String> lines){
		String table = null;
		
		for(String line : lines){
			Matcher m = tableNamePattern.matcher(line.toUpperCase().replaceAll("IF\\s+NOT\\s+EXISTS", ""));
			
			if(m.matches() && m.groupCount() > 0){
				table = m.group(1);
				break;
			}
		}
		
		return table;
	}
	
	protected static List<Column> getColumns(List<String> lines){
		List<Column> columns = new ArrayList<>();
		
		List<String> pks = new ArrayList<>();
		
		// scan primary keys
		for(String line : lines){
			Matcher pkMatcher = primaryKeyPattern.matcher(line);
			if(pkMatcher.matches()){
				String[] keys = pkMatcher.group("keys").replaceAll("`", "").split(",");
				for(String key : keys){
					pks.add(key.trim().toLowerCase());
				}
			}
		}
		
		for(String line : lines){
			Matcher columnNameMatcher = columnNamePattern.matcher(line);
			
			if(columnNameMatcher.matches() && columnNameMatcher.groupCount() > 1){
				Column column = new Column();
				
				String name = columnNameMatcher.group("name");
				
				column.setName(name);
				if(!COLUMN_EX_KEYS.contains(name.toLowerCase())){
					column.setType(columnNameMatcher.group("type").toLowerCase());
					column.setLength(columnNameMatcher.group("size"));
					
					if(StringUtils.isNotBlank(columnNameMatcher.group("sign"))){
						column.setUnsigned(true);
					}
					
//					if(line.toUpperCase() ==~ /.+NOT\s+NULL.+/){
					if(line.toUpperCase().matches(".+NOT\\s+NULL.+")){
						column.setNullable(false);
					}
					
//					if(line.toUpperCase() ==~ /.+\s+DEFAULT\s+.+/){
					if(line.toUpperCase().matches(".+\\s+DEFAULT\\s+.+")){
						int i = line.toUpperCase().indexOf(" DEFAULT ");
						String text = line.substring(i + 9, line.length());
						column.setDefaultValue(StringUtils.replace(text.split("\\s+")[0], "'", ""));
					}
				
					if(line.toLowerCase().contains(MySqlKeyWords.AUTO_INCREMENT)){
						column.setAutoIncrement(true);
					}
					
					if(pks.contains(name.toLowerCase())){
						column.setPrimary(true);
					}
					
					String[] parts = line.split("\\s+");
					if(ArrayUtils.isNotEmpty(parts)){
						int commentIndex = -1;
						for(int i = parts.length - 1; i >= 0; i--){
							if("comment".equalsIgnoreCase(parts[i])){
								commentIndex = i;
								break;
							}
						}
						
						if(commentIndex != -1 && parts.length > commentIndex + 1){
							String comment = parts[commentIndex + 1];
							comment = StringUtils.replace(comment, ",", "");
							comment = StringUtils.replace(comment, "'", "");
							column.setComment(comment);
						}
					}
					
					columns.add(column);
				}
			}
		}
		
		return columns;
	}
	
	private DdlParser(){}
}
